Click Here!
home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96

Bookmark It

Search this book:
 
Previous Table of Contents Next


Chapter 2
Understanding Terms

Chapter 1 gave an introduction to Oracle. That introduction gave you a better understanding of how the RDBMS engine itself works as well as some insight into how you can use that knowledge to effectively design an optimal system.

In following the philosophy of building on a foundation of knowledge, this chapter discusses some important terms used throughout the book. Because these terms and descriptions are used frequently, I think it is appropriate to provide these definitions early in the book.

This chapter is split into several sections. The first section describes some terms frequently used when discussing database engineering and databases. The second section involves further explanations of some of the internal workings of an RDBMS. The final section describes some of the business models RDBMS systems are commonly used for. The last section reviews the unit conversions used in the book.

Terms

Before starting any explanation of the functions of Oracle, I would like to list a few terms that are used throughout the book. These terms are frequently used in the industry; for newcomers, clear definitions are not always given. Because many of these terms are not related, they are listed in alphabetical order. These and many other terms are also listed in the glossary in Appendix D.

ad-hoc.
From the Latin this is. This term is used to describe an impromptu or spontaneous action. Most commonly used in terms of an ad-hoc query to mean an impromptu, simple query.
Asynchronous I/O (AIO).
Asynchronous I/O allows a process to submit an I/O and not have to wait for the response. Later, when the I/O is completed, an interrupt occurs or the process can check to see whether the I/O has completed. By using Asynchronous I/Os, the DBWR can manage multiple writes at once so that it is not starved waiting for I/Os to complete.
block.
The smallest unit of storage in an Oracle database. The database block contains header information concerning the block itself as well as the data.
buffer.
An amount of memory used to store data. A buffer stores data that is about to be used or that has just been used. In many cases, buffers are in-memory copies of data that is also on disk. Buffers can be used as a copy of data for quick read-access, they can be modified and written to disk, or they can be created in memory as temporary storage.
In Oracle, the database buffers of the SGA store the most recently used blocks of database data. The set of database block buffers is known as the database buffer cache. The buffers used to temporarily store redo entries until they can be written to disk are known as the redo log buffers.
A clean buffer is a buffer that has not been modified. Because this buffer has not been changed, it is not necessary for the DBWR to write this buffer to disk. A dirty buffer is a buffer that has been modified. It is the job of the DBWR to eventually write all dirty block buffers out to disk.
cache.
A storage area used to provide fast access to data. In hardware terms, the cache is a small (relative to main RAM) amount of memory that is much faster than main memory. This memory is used to reduce the time it takes to reload frequently used data or instructions into the CPU. CPU chips themselves contain small amounts of memory built in as a cache.
In Oracle, the block buffers and shared pool are considered caches because they are used to store data and instructions for quick access. Caching is very effective in reducing the time it takes to retrieve frequently used data.
Caching usually works using a least recently used algorithm. Data that has not been used for a while is eventually released from the cache to make room for new data. If data is requested and is in the cache (a phenomenon called a cache hit), the data is retrieved from the cache, avoiding having to retrieve it from memory or disk. Once the data has been accessed again, it is marked as recently used and put on the top of the cache list.
checksum.
A number calculated from the contents of a storage unit such as a file or data block. Using a mathematical formula, the checksum number is generated from data. Because it is highly unlikely that data corruption can occur in such a way that the checksum would remain the same, checksums are used to verify data integrity. Beginning with Oracle version 7.2, checksums can be enabled on both data blocks and redo blocks.
concurrency.
The ability to perform many functions at the same time. Oracle provides for concurrency by allowing many users to access the database simultaneously.
contention.
A term usually used to describe a condition that occurs when two or more processes or threads attempt to obtain the same resource. The results of contention can vary depending on the resource in question.
cursor.
A handle to a specific private SQL area. You can think of a cursor as a pointer to or a name of a particular private SQL area.
data dictionary.
A set of tables Oracle uses to maintain information about the database. The data dictionary contains information about tables, indexes, clusters, and so on.
DDL (Data Definition Language) commands.
The commands used in the creation and modification of schema objects. These commands include the ability to create, alter, and drop objects; grant and revoke privileges and roles; establish auditing options; and add comments to the data dictionary. These commands are all related to the management and administration of the Oracle database. Before and after each DDL statement, Oracle implicitly commits the current transaction.
DML (Data Manipulation Language) commands.
The commands that allow you to query and modify data within existing schema objects. Unlike the DDL commands, a commit is not implicit. DML statements consist of DELETE, INSERT, SELECT, and UPDATE statements; EXPLAIN PLAN statements; and LOCK TABLE statements.
DBA (Database Administrator).
The person responsible for the operation and configuration of the database. The DBA is the person responsible for the performance of the database. The DBA is charged with keeping the database operating smoothly, ensuring that backups are done on a regular basis (and that the backups work), and installing new software. Other responsibilities may include planning for future expansion and disk space needs; creating databases and tablespaces; adding users and maintaining security; and monitoring the database and retuning it as necessary. Large installations may have teams of DBAs to keep the system running smoothly; alternatively, the tasks may be segmented among the DBAs.
disk array.
A set of two or more disks that can appear to the system as one large disk. A disk array can be either a software or a hardware device.
dynamic performance tables.
Tables created at instance startup and used to store information about the performance of the instance. This information includes connection information, I/Os, initialization parameter values, and so on.
function.
A set of SQL or PL/SQL statements used together to execute a particular function. Procedures and functions are identical except that functions always return a value (procedures do not). By processing the SQL code on the database server, you can reduce the amount of instructions sent across the network and returned from the SQL statements.
logical disk.
A term used to describe a disk that is in reality two or more disks in a hardware or software disk array. To the user, it appears as one large disk when in reality it is two or more striped physical disks.
package.
A collection of related, stored procedures or functions grouped together.
paging.
An operating system function used to copy virtual memory between physical memory and the paging file (see virtual memory, later in this list). Paging is used when the amount of virtual memory in use has exceeded the amount of physical memory available. Paging is an expensive task in terms of performance and should be avoided if possible.
physical memory.
The actual hardware RAM (Random Access Memory) available in the computer for use by the operating system and applications.
procedure.
A set of SQL or PL/SQL statements used together to execute a particular function. Procedures and functions are identical except that functions always return a value (procedures do not). By processing the SQL code on the database server, you can reduce the amount of instructions sent across the network and returned from the SQL statements.
program unit.
In Oracle, the term used to describe a package, a stored procedure, or a sequence.
random I/O.
Occurs when data is accessed on a disk drive in no specific order. Random I/O typically creates significant disk head movement.
read consistency.
An attribute used to ensure that, during a SQL statement, data returned from Oracle is consistent. Oracle uses the rollback segments to ensure read consistency.
scalabilty.
Typically used in association with multiprocessor or cluster configurations. The scalability of the additional component refers to the performance gain obtained by adding that component. A perfectly scaleable solution gives double the performance when you add a second component.
For example, if you have an SMP machine with a measured performance of 1.0 (normalized), add a second CPU, and get a performance of 1.9, the scalability of adding the second CPU is 1.9 or 90 percent. This term is used quite frequently in hardware and software manufacturer’s literature when marketing multiprocessor or clustered solutions.
schema.
A collection of objects associated with the database.
schema objects.
Abstractions or logical structures that refer to database objects or structures. Schema objects consist of such things as clusters, indexes, packages, sequences, stored procedures, synonyms, tables, views, and so on.
sequential I/O.
Occurs when data is accessed on a disk drive in order. Sequential I/O typically causes very little disk head movement.
SGA.
See System Global Area.
swapping.
An operating system function similar to paging; used to copy virtual memory between physical memory and the paging file (see virtual memory, later in this list). Swapping is almost identical to paging except that swapping is done on a process basis and paging is done on a memory-page basis. Swapping is used when the amount of virtual memory in use has exceeded the amount of physical memory available. Swapping is quite expensive in terms of performance and should be avoided if possible.
System Global Area.
The SGA is a shared memory region Oracle uses to store data and control information for a single Oracle instance. The SGA is allocated when the Oracle instance starts; it is deallocated when the Oracle instance shuts down. Each Oracle instance that starts has its own SGA. The information in the SGA is made up of the database buffers, the redo log buffer, and the shared pool; each has a fixed size and is created at instance startup.
trigger.
A mechanism that allows you to write procedures that are automatically executed whenever an INSERT, UPDATE, or DELETE statement is executed on a table or view. Triggers can be used to enforce integrity constraints or automate some other custom function.
virtual memory.
The memory that can be used for programs in the operating system. To overcome the limitations associated with insufficient physical memory, virtual memory allows programs to run that are larger than the amount of physical memory in the system. When there is not enough physical memory in the system, these programs are copied from RAM to a disk file called a paging or swap file. This arrangement allows small systems to run many programs. There is a performance penalty you pay when the computer pages or swaps.
transaction.
A logical unit of work consisting of one or more SQL statements, ending in a commit or a rollback. Performance measurements, as described in Chapter 5, “Benchmarking,” often use the number of transactions per second as the performance metric.


Previous Table of Contents Next


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited.